package com.batchengine.db;

import com.batchengine.util.BatchPropertyReader;
import org.apache.commons.dbcp.ConnectionFactory;
import org.apache.commons.dbcp.DriverManagerConnectionFactory;
import org.apache.commons.dbcp.PoolableConnectionFactory;
import org.apache.commons.dbcp.PoolingDataSource;
import org.apache.commons.pool.impl.GenericObjectPool;
import org.apache.log4j.Logger;

import javax.sql.DataSource;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import static com.batchengine.util.BatchPropertyReader.getBatchReader;
import static com.batchengine.util.LoggingFactory.createLogger;
import static java.lang.Class.forName;
import static java.lang.Integer.parseInt;
import static org.apache.commons.pool.impl.GenericObjectPool.WHEN_EXHAUSTED_BLOCK;

/**
 * Author  : SunilKalva
 * User     : skalva@ymail.com
 * Date     : Apr 5, 2008
 * <p/>
 * Copyright (c) 2007 Incubator Incorporated. All Rights Reserved.
 * <p/>
 * This software is the confidential and proprietary information of
 * Incubator, Inc. Use is subject to license terms.
 */

public final class DBUtils {

    private static final Logger logger = createLogger();

    private static DataSource ds = null;

    static {

        try {
            forName(getBatchReader().getProperty("jdbc.driverClassName"));
        } catch(ClassNotFoundException e) {
            logger.error("error in loading mysql driver");
        }
        getDataSource();
    }

    public static DataSource getDataSource() {

        if(null != ds) {
            return ds;
        }

        try {

            GenericObjectPool connectionPool = new GenericObjectPool(null);
            connectionPool.setMaxActive(parseInt(getBatchReader().getProperty("jdbc.pool.max.active.size")));
            connectionPool.setMaxIdle(parseInt(getBatchReader().getProperty("jdbc.pool.max.idle.size")));
            connectionPool.setMinIdle(parseInt(getBatchReader().getProperty("jdbc.pool.min.idle.size")));
            connectionPool.setMaxWait(1000 * 5);
            connectionPool.setWhenExhaustedAction(WHEN_EXHAUSTED_BLOCK);

            ConnectionFactory connectionFactory = new DriverManagerConnectionFactory(
                    getBatchReader().getProperty("jdbc.url"),
                    getBatchReader().getProperty("jdbc.username"),
                    getBatchReader().getProperty("jdbc.password"));

            PoolableConnectionFactory poolableConnectionFactory = new PoolableConnectionFactory(connectionFactory, connectionPool, null, null, false, true);
            ds = new PoolingDataSource(poolableConnectionFactory.getPool());
        } catch(Exception e) {
            logger.error("\nException occured while getting the Datasource ", e);
            throw new RuntimeException("Exception occured while getting the Datasource", e);
        }
        return ds;
    }

    public static Connection getConnection() {

        try {
            return ds.getConnection();
        } catch(Exception e) {
            logger.error("\nException occured while getting the connection ", e);
        }
        return null;
    }

    /**
     * Close a <code>Connection</code>, avoid closing if null.
     *
     * @param conn Connection to close.
     *
     * @throws SQLException if a database access error occurs
     */
    public static void close(Connection conn) {

        if(conn != null) {
            try {

                conn.close();
            } catch(SQLException e) {
                logger.error("error in closing connection ", e);
            }
        }
    }

    /**
     * Close a <code>ResultSet</code>, avoid closing if null.
     *
     * @param rs ResultSet to close.
     *
     * @throws SQLException if a database access error occurs
     */
    public static void close(ResultSet rs) throws SQLException {
        if(rs != null) {
            rs.close();
        }
    }

    /**
     * Close a <code>Statement</code>, avoid closing if null.
     *
     * @param stmt Statement to close.
     *
     * @throws SQLException if a database access error occurs
     */
    public static void close(Statement stmt) throws SQLException {
        if(stmt != null) {
            stmt.close();
        }
    }

    /**
     * Close a <code>Connection</code>, avoid closing if null and hide
     * any SQLExceptions that occur.
     *
     * @param conn Connection to close.
     */
    public static void closeQuietly(Connection conn) {
        close(conn);
    }

    /**
     * Close a <code>Connection</code>, <code>Statement</code> and
     * <code>ResultSet</code>.  Avoid closing if null and hide any
     * SQLExceptions that occur.
     *
     * @param conn Connection to close.
     * @param stmt Statement to close.
     * @param rs   ResultSet to close.
     */
    public static void closeQuietly(Connection conn, Statement stmt, ResultSet rs) {

        try {
            closeQuietly(rs);
        } finally {
            try {
                closeQuietly(stmt);
            } finally {
                closeQuietly(conn);
            }
        }

    }

    /**
     * Close a <code>ResultSet</code>, avoid closing if null and hide any
     * SQLExceptions that occur.
     *
     * @param rs ResultSet to close.
     */
    public static void closeQuietly(ResultSet rs) {
        try {
            close(rs);
        } catch(SQLException e) {
            // quiet
        }
    }

    /**
     * Close a <code>Statement</code>, avoid closing if null and hide
     * any SQLExceptions that occur.
     *
     * @param stmt Statement to close.
     */
    public static void closeQuietly(Statement stmt) {
        try {
            close(stmt);
        } catch(SQLException e) {
            // quiet
        }
    }

    /**
     * Commits a <code>Connection</code> then closes it, avoid closing if null.
     *
     * @param conn Connection to close.
     *
     * @throws SQLException if a database access error occurs
     */
    public static void commitAndClose(Connection conn) throws SQLException {
        if(conn != null) {
            try {
                conn.commit();
            } finally {
                conn.close();
            }
        }
    }

    /**
     * Commits a <code>Connection</code> then closes it, avoid closing if null
     * and hide any SQLExceptions that occur.
     *
     * @param conn Connection to close.
     */
    public static void commitAndCloseQuietly(Connection conn) {
        try {
            commitAndClose(conn);
        } catch(SQLException e) {
            // quiet
        }
    }

    /**
     * Loads and registers a database driver class.
     * If this succeeds, it returns true, else it returns false.
     *
     * @param driverClassName of driver to load
     *
     * @return boolean <code>true</code> if the driver was found, otherwise <code>false</code>
     */
    public static boolean loadDriver(String driverClassName) {
        try {
            forName(driverClassName).newInstance();
            return true;

        } catch(ClassNotFoundException e) {
            return false;

        } catch(IllegalAccessException e) {
            // Constructor is private, OK for DriverManager contract
            return true;

        } catch(InstantiationException e) {
            return false;

        } catch(Throwable e) {
            return false;
        }
    }

    /**
     * Print the stack trace for a SQLException to STDERR.
     *
     * @param e SQLException to print stack trace of
     */
    public static void printStackTrace(SQLException e) {
        printStackTrace(e, new PrintWriter(System.err));
    }

    /**
     * Print the stack trace for a SQLException to a
     * specified PrintWriter.
     *
     * @param e  SQLException to print stack trace of
     * @param pw PrintWriter to print to
     */
    public static void printStackTrace(SQLException e, PrintWriter pw) {

        SQLException next = e;
        while(next != null) {
            next.printStackTrace(pw);
            next = next.getNextException();
            if(next != null) {
                pw.println("Next SQLException:");
            }
        }
    }

    /**
     * Print warnings on a Connection to STDERR.
     *
     * @param conn Connection to print warnings from
     */
    public static void printWarnings(Connection conn) {
        printWarnings(conn, new PrintWriter(System.err));
    }

    /**
     * Print warnings on a Connection to a specified PrintWriter.
     *
     * @param conn Connection to print warnings from
     * @param pw   PrintWriter to print to
     */
    public static void printWarnings(Connection conn, PrintWriter pw) {
        if(conn != null) {
            try {
                printStackTrace(conn.getWarnings(), pw);
            } catch(SQLException e) {
                printStackTrace(e, pw);
            }
        }
    }

    /**
     * Rollback any changes made on the given connection.
     *
     * @param conn Connection to rollback.  A null value is legal.
     *
     * @throws SQLException if a database access error occurs
     */
    public static void rollback(Connection conn) throws SQLException {
        if(conn != null) {
            conn.rollback();
        }
    }

    /**
     * Performs a rollback on the <code>Connection</code> then closes it,
     * avoid closing if null.
     *
     * @param conn Connection to rollback.  A null value is legal.
     *
     * @throws SQLException if a database access error occurs
     * @since DbUtils 1.1
     */
    public static void rollbackAndClose(Connection conn) throws SQLException {
        if(conn != null) {
            try {
                conn.rollback();
            } finally {
                conn.close();
            }
        }
    }

    /**
     * Performs a rollback on the <code>Connection</code> then closes it,
     * avoid closing if null and hide any SQLExceptions that occur.
     *
     * @param conn Connection to rollback.  A null value is legal.
     *
     * @since DbUtils 1.1
     */
    public static void rollbackAndCloseQuietly(Connection conn) {
        try {
            rollbackAndClose(conn);
        } catch(SQLException e) {
            // quiet
        }
    }

}
